--Tabela PURGE_CONFIG não encontrada e deve ser criada.
CREATE OR REPLACE FUNCTION func_createTable() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(pc.*) INTO VCOUNT FROM pg_class pc, pg_namespace pn WHERE pc.relname = 'purge_config' AND pc.relnamespace = pn.oid AND pn.nspname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE TABLE <SCHEMA>.PURGE_CONFIG (
    COD_PURGE_CONFIG NUMERIC(20, 0) NOT NULL,
    CRON_EXPRESSION VARCHAR(255),
    DAYS_TO_KEEP NUMERIC(10, 0) NOT NULL,
    DESCR VARCHAR(255),
    ENABLED BOOL NOT NULL,
    MAX_WINDOW_SIZE NUMERIC(10, 0) NOT NULL,
    NAME VARCHAR(255) NOT NULL,
    PURGER_BEAN_NAME VARCHAR(255) NOT NULL
);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createTable();
 DROP FUNCTION func_createTable();


--Tabela PURGE_REQUEST não encontrada e deve ser criada.
CREATE OR REPLACE FUNCTION func_createTable() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(pc.*) INTO VCOUNT FROM pg_class pc, pg_namespace pn WHERE pc.relname = 'purge_request' AND pc.relnamespace = pn.oid AND pn.nspname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE TABLE <SCHEMA>.PURGE_REQUEST (
    COD_PURGE_REQUEST NUMERIC(20, 0) NOT NULL,
    ERROR_MESSAGE VARCHAR(255),
    MAX_DATE DATE NOT NULL,
    MAX_WINDOW_SIZE NUMERIC(10, 0) NOT NULL,
    PURGE_CONFIG_NAME VARCHAR(255) NOT NULL,
    ST NUMERIC(1, 0) NOT NULL,
    TOTAL_ITEMS NUMERIC(10, 0) NOT NULL,
    TOTAL_ITEMS_PURGED NUMERIC(10, 0) NOT NULL,
    TS_FINISH TIMESTAMP,
    TS_START TIMESTAMP NOT NULL,
    USERNAME VARCHAR(255) NOT NULL
);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createTable();
 DROP FUNCTION func_createTable();


--Chave primária PK_PURCFG não encontrada.
\echo 'Incluindo PK PK_PURCFG na Tabela PURGE_CONFIG'

CREATE OR REPLACE FUNCTION func_dropToCreatePrimeryKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.purge_config'::REGCLASS AND conname= 'pk_purcfg';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.PURGE_CONFIG DROP CONSTRAINT PK_PURCFG;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreatePrimeryKey();
 DROP FUNCTION func_dropToCreatePrimeryKey();


ALTER TABLE <SCHEMA>.PURGE_CONFIG ADD CONSTRAINT PK_PURCFG PRIMARY KEY (COD_PURGE_CONFIG);
--Unique constraint AK_PURCFG_JT_0 não encontrado.
\echo 'Incluindo/Alterando AK_PURCFG_JT_0 na Tabela PURGE_CONFIG'

\echo 'Excluindo AK_PURCFG_JT_0 da Tabela PURGE_CONFIG'

CREATE OR REPLACE FUNCTION func_dropUniqueConstraint() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.purge_config'::REGCLASS AND conname= 'ak_purcfg_jt_0';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.PURGE_CONFIG DROP CONSTRAINT AK_PURCFG_JT_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropUniqueConstraint();
 DROP FUNCTION func_dropUniqueConstraint();


ALTER TABLE <SCHEMA>.PURGE_CONFIG ADD CONSTRAINT AK_PURCFG_JT_0 UNIQUE (NAME);

--Comment não encontrado.
COMMENT ON TABLE <SCHEMA>.PURGE_CONFIG IS 'CONFIGURAÇÃO DE EXPURGOS.';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_CONFIG.COD_PURGE_CONFIG IS 'CÓDIGO DO EXPURGO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_CONFIG.CRON_EXPRESSION IS 'PERIODICIDADE DE EXECUÇÃO DE EXPURGO EM FORMATO DE EXPRESSÃO CRON';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_CONFIG.DAYS_TO_KEEP IS 'DIAS PARA MANTER';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_CONFIG.DESCR IS 'DESCRIÇÃO DO EXPURGO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_CONFIG.ENABLED IS 'INDICA SE ESSA CONFIGURAÇÃO DE EXPURGO ESTÁ ATIVA OU NÃO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_CONFIG.MAX_WINDOW_SIZE IS 'TAMANHO MÁXIMO DA JANELA DE EXPURGO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_CONFIG.NAME IS 'NOME ÚNICO DA CONFIGURAÇÃO DO EXPURGO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_CONFIG.PURGER_BEAN_NAME IS 'NOME DO BEAN DO SPRING QUE EXECUTA BUSINESS ESPECÍFICO DE EXPURGO';
--Chave primária PK_PURREQ não encontrada.
\echo 'Incluindo PK PK_PURREQ na Tabela PURGE_REQUEST'

CREATE OR REPLACE FUNCTION func_dropToCreatePrimeryKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.purge_request'::REGCLASS AND conname= 'pk_purreq';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.PURGE_REQUEST DROP CONSTRAINT PK_PURREQ;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropToCreatePrimeryKey();
 DROP FUNCTION func_dropToCreatePrimeryKey();


ALTER TABLE <SCHEMA>.PURGE_REQUEST ADD CONSTRAINT PK_PURREQ PRIMARY KEY (COD_PURGE_REQUEST);
--Comment não encontrado.
COMMENT ON TABLE <SCHEMA>.PURGE_REQUEST IS 'LOG DE REQUISIÇÃO DE EXPURGO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_REQUEST.COD_PURGE_REQUEST IS 'CÓDIGO DA EXECUÇÃO DE EXPURGO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_REQUEST.ERROR_MESSAGE IS 'MENSAGEM DE ERRO CASO ESSA REQUISIÇÃO DE EXECUÇÃO DE EXPURGO TENHA TERMINADO EM ERRO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_REQUEST.MAX_DATE IS 'DATA MÁXIMA CONSIDERADA PARA EXECUÇÃO DE EXPURGO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_REQUEST.MAX_WINDOW_SIZE IS 'TAMANHO MÁXIMO DE JANELA DE EXPURGO USADO NESSA EXECUÇÃO. PODE SER DIFERENTE DO VALOR ATUAL DA CONFIGURAÇÃO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_REQUEST.PURGE_CONFIG_NAME IS 'NOME DA CONFIGURAÇÃO DE EXPURGO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_REQUEST.ST IS 'STATUS DA REQUISIÇÃO DE EXPURGO. 0. RUNNING, 1. OK, 2. ERROR';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_REQUEST.TOTAL_ITEMS IS 'QUANTIDADE DE ITEMS TOTAL DESSA REQUISIÇÃO DE EXPURGO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_REQUEST.TOTAL_ITEMS_PURGED IS 'QUANTIDADE DE ITEMS EXPURGADOS';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_REQUEST.TS_FINISH IS 'DATA/HORA DO TÉRMINO DA EXECUÇÃO DO EXPURGO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_REQUEST.TS_START IS 'DATA/HORA DO INÍCIO DA EXECUÇÃO DO EXPURGO';
--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.PURGE_REQUEST.USERNAME IS 'NOME DO USUÁRIO QUE EXECUTOU EXPURGO';
--Foreign key FK_EXCREC_HYDREQ não deveria existir.
\echo 'Excluindo FK FK_EXCREC_HYDREQ da Tabela HYDRA_REQUEST'

CREATE OR REPLACE FUNCTION func_dropForeignKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.hydra_request'::REGCLASS AND conname= 'fk_excrec_hydreq';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.HYDRA_REQUEST DROP CONSTRAINT FK_EXCREC_HYDREQ;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropForeignKey();
 DROP FUNCTION func_dropForeignKey();


--Foreign key FK_HYDREQ_HYDMSG não deveria existir.
\echo 'Excluindo FK FK_HYDREQ_HYDMSG da Tabela HYDRA_MESSAGE'

CREATE OR REPLACE FUNCTION func_dropForeignKey() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.hydra_message'::REGCLASS AND conname= 'fk_hydreq_hydmsg';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.HYDRA_MESSAGE DROP CONSTRAINT FK_HYDREQ_HYDMSG;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropForeignKey();
 DROP FUNCTION func_dropForeignKey();


--Check constraint CK_PURREQ_0 não encontrado.
\echo 'Incluindo CK_PURREQ_0 na Tabela PURGE_REQUEST'

CREATE OR REPLACE FUNCTION func_dropCheckConstraint() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_constraint WHERE conrelid='<SCHEMA>.purge_request'::REGCLASS AND conname= 'ck_purreq_0';

      IF VCOUNT = 1 THEN
          ALTER TABLE <SCHEMA>.PURGE_REQUEST DROP CONSTRAINT CK_PURREQ_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropCheckConstraint();
 DROP FUNCTION func_dropCheckConstraint();


ALTER TABLE <SCHEMA>.PURGE_REQUEST ADD CONSTRAINT CK_PURREQ_0 CHECK ((ST = ANY (ARRAY[(0)::NUMERIC, (1)::NUMERIC, (2)::NUMERIC])));


--Index IX_PURREQ_JT_0 não encontrado.
\echo 'Criando Index IX_PURREQ_JT_0'

CREATE OR REPLACE FUNCTION func_dropIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'IX_PURREQ_JT_0' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 1 THEN
          DROP INDEX IX_PURREQ_JT_0;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_dropIndexElement();
 DROP FUNCTION func_dropIndexElement();

CREATE OR REPLACE FUNCTION func_createIndexElement() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_indexes WHERE indexname = 'ix_purreq_jt_0' AND schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE INDEX IX_PURREQ_JT_0 ON <SCHEMA>.PURGE_REQUEST (TS_START ASC);
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createIndexElement();
 DROP FUNCTION func_createIndexElement();

--Coluna COD_HYDRA_REQUEST está errada na tabela HYDRA_MESSAGE.
\echo 'Alterando coluna HYDRA_MESSAGE.COD_HYDRA_REQUEST'

CREATE OR REPLACE FUNCTION func_alterColumn() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT count(*) into VCOUNT FROM pg_attribute WHERE attrelid='<SCHEMA>.hydra_message'::REGCLASS AND attname = 'cod_hydra_request';
      IF VCOUNT = 1 THEN
                    ALTER TABLE <SCHEMA>.HYDRA_MESSAGE ALTER COLUMN COD_HYDRA_REQUEST TYPE NUMERIC(20, 0), ALTER COLUMN COD_HYDRA_REQUEST DROP NOT NULL;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_alterColumn();
 DROP FUNCTION func_alterColumn();


--Comment não encontrado.
COMMENT ON COLUMN <SCHEMA>.HYDRA_MESSAGE.COD_HYDRA_REQUEST IS 'CODIGO DA REQUISICAO QUE TRATOU ESTA MENSAGEM';


--Sequence SEQ_PURCFG está faltando.
\echo 'Incluindo Sequence SEQ_PURCFG'

CREATE OR REPLACE FUNCTION func_createSequence() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_statio_all_sequences WHERE relname = 'seq_purcfg' and schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE SEQUENCE <SCHEMA>.SEQ_PURCFG START WITH 100000;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createSequence();
 DROP FUNCTION func_createSequence();

--Sequence SEQ_PURREQ está faltando.
\echo 'Incluindo Sequence SEQ_PURREQ'

CREATE OR REPLACE FUNCTION func_createSequence() RETURNS void AS $$
DECLARE
    VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM pg_statio_all_sequences WHERE relname = 'seq_purreq' and schemaname = '<SCHEMA>';

      IF VCOUNT = 0 THEN
          CREATE SEQUENCE <SCHEMA>.SEQ_PURREQ START WITH 100000;
      END IF;
      RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_createSequence();
 DROP FUNCTION func_createSequence();

CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.PURGE_CONFIG WHERE COD_PURGE_CONFIG = 1;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.PURGE_CONFIG SET COD_PURGE_CONFIG = 1, CRON_EXPRESSION = '0 4 * * *', DAYS_TO_KEEP = 30, DESCR = 'EXPURGO DO LOG DO EXPURGO', ENABLED = true, MAX_WINDOW_SIZE = 1000, NAME = 'LOG DO EXPURGO', PURGER_BEAN_NAME = 'purgeRequestPurger' WHERE COD_PURGE_CONFIG = 1;
  ELSE
    INSERT INTO <SCHEMA>.PURGE_CONFIG (COD_PURGE_CONFIG, CRON_EXPRESSION, DAYS_TO_KEEP,                       DESCR, ENABLED, MAX_WINDOW_SIZE,             NAME,     PURGER_BEAN_NAME)
                               VALUES (               1,     '0 4 * * *',           30, 'EXPURGO DO LOG DO EXPURGO',    true,            1000, 'LOG DO EXPURGO', 'purgeRequestPurger');
  END IF;
  RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();

CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.PURGE_CONFIG WHERE COD_PURGE_CONFIG = 2;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.PURGE_CONFIG SET COD_PURGE_CONFIG = 2, CRON_EXPRESSION = '0 4 * * *', DAYS_TO_KEEP = 30, DESCR = 'EXPURGO DO RTM', ENABLED = true, MAX_WINDOW_SIZE = 1000, NAME = 'RTM', PURGER_BEAN_NAME = 'rtmPurger' WHERE COD_PURGE_CONFIG = 2;
  ELSE
    INSERT INTO <SCHEMA>.PURGE_CONFIG (COD_PURGE_CONFIG, CRON_EXPRESSION, DAYS_TO_KEEP,            DESCR, ENABLED, MAX_WINDOW_SIZE,  NAME, PURGER_BEAN_NAME)
                               VALUES (               2,     '0 4 * * *',           30, 'EXPURGO DO RTM',    true,            1000, 'RTM', 'rtmPurger');
  END IF;
  RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();

CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.PURGE_CONFIG WHERE COD_PURGE_CONFIG = 3;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.PURGE_CONFIG SET COD_PURGE_CONFIG = 3, CRON_EXPRESSION = '0 4 * * *', DAYS_TO_KEEP = 30, DESCR = 'EXPURGO DO EXECUTIONLOG', ENABLED = true, MAX_WINDOW_SIZE = 500, NAME = 'EXECUTIONLOG', PURGER_BEAN_NAME = 'executionLogPurger' WHERE COD_PURGE_CONFIG = 3;
  ELSE
    INSERT INTO <SCHEMA>.PURGE_CONFIG (COD_PURGE_CONFIG, CRON_EXPRESSION, DAYS_TO_KEEP,                     DESCR, ENABLED, MAX_WINDOW_SIZE,           NAME, PURGER_BEAN_NAME)
                               VALUES (               3,     '0 4 * * *',           30, 'EXPURGO DO EXECUTIONLOG',    true,             500, 'EXECUTIONLOG', 'executionLogPurger');
  END IF;
  RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();

CREATE OR REPLACE FUNCTION func_insert_or_update() RETURNS void AS $$
DECLARE
  VCOUNT  INTEGER := 0;
BEGIN
  SELECT COUNT(*) INTO VCOUNT FROM <SCHEMA>.PURGE_CONFIG WHERE COD_PURGE_CONFIG = 4;
  IF VCOUNT = 1 THEN
    UPDATE <SCHEMA>.PURGE_CONFIG SET COD_PURGE_CONFIG = 4, CRON_EXPRESSION = '0 3 * * *', DAYS_TO_KEEP = 30, DESCR = 'EXPURGO DE REQUISIÇÕES DO HYDRA', ENABLED = true, MAX_WINDOW_SIZE = 100, NAME = 'HYDRA', PURGER_BEAN_NAME = 'hydraRequestPurger' WHERE COD_PURGE_CONFIG = 4;
  ELSE
    INSERT INTO <SCHEMA>.PURGE_CONFIG (COD_PURGE_CONFIG, CRON_EXPRESSION, DAYS_TO_KEEP,                             DESCR, ENABLED, MAX_WINDOW_SIZE,    NAME,     PURGER_BEAN_NAME)
                               VALUES (               4,     '0 3 * * *',           30, 'EXPURGO DE REQUISIÇÕES DO HYDRA',    true,             100, 'HYDRA', 'hydraRequestPurger');
  END IF;
  RETURN;
END;

$$ LANGUAGE plpgsql;
SELECT func_insert_or_update();
 DROP FUNCTION func_insert_or_update();
